{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Calculating Historical Free Cash Flows" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In this exercise we will load income statement and balance sheet data and use them to calculate free cash flows. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Load Financial Statements into `DataFrame`s\n", "\n", "First we will use `pandas`' `read_excel` to get the data into `DataFrame`s." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "\n", "all_statements_path = 'Exxon Mobil Corporation NYSE XOM Financials.xls'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Income Statement" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "\n", "inc_df = pd.read_excel(all_statements_path, sheet_name='Income Statement')" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Unnamed: 0Dec-31-2014Dec-31-2015Dec-31-2016Dec-31-2017Dec-31-2018Sep-30-2019
0NaNNaNNaNNaNNaNNaN
1Revenue364763239854200628237162279332260812
2Other Revenue-1552----
3Total Revenue364763241406200628237162279332260812
4NaNNaNNaNNaNNaNNaNNaN
\n", "
" ], "text/plain": [ " Unnamed: 0 Dec-31-2014 Dec-31-2015 Dec-31-2016 Dec-31-2017 \\\n", "0 NaN NaN NaN NaN \n", "1 Revenue 364763 239854 200628 237162 \n", "2 Other Revenue - 1552 - - \n", "3 Total Revenue 364763 241406 200628 237162 \n", "4 NaN NaN NaN NaN NaN \n", "\n", " Dec-31-2018 Sep-30-2019 \n", "0 NaN NaN \n", "1 279332 260812 \n", "2 - - \n", "3 279332 260812 \n", "4 NaN NaN " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see this is a little messy. We can clean this up first by setting the index to be the first column. You can pass an integer index for a column to say that that column should be used as the index of the `DataFrame`. Here we want this first column, so we will pass `index_col=0` into `read_excel`." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "inc_df = pd.read_excel(all_statements_path, sheet_name='Income Statement', index_col=0)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Dec-31-2014Dec-31-2015Dec-31-2016Dec-31-2017Dec-31-2018Sep-30-2019
NaNNaNNaNNaNNaNNaN
Revenue364763239854200628237162279332260812
Other Revenue-1552----
Total Revenue364763241406200628237162279332260812
NaNNaNNaNNaNNaNNaNNaN
\n", "
" ], "text/plain": [ " Dec-31-2014 Dec-31-2015 Dec-31-2016 Dec-31-2017 Dec-31-2018 \\\n", " NaN NaN NaN NaN NaN \n", "Revenue 364763 239854 200628 237162 279332 \n", "Other Revenue - 1552 - - - \n", " Total Revenue 364763 241406 200628 237162 279332 \n", "NaN NaN NaN NaN NaN NaN \n", "\n", " Sep-30-2019 \n", " NaN \n", "Revenue 260812 \n", "Other Revenue - \n", " Total Revenue 260812 \n", "NaN NaN " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "That's starting to look better. But we can see that there are some empty rows in the data. We can also see that there is a `-` in some values where there should be missing data. We want to remove the rows without any data, but first we want to fill in missing values when there is `-`, so that we can make sure a row of completely `-` would get removed. The missing representation in `pandas` is `NaN`, which we can specify manually through `numpy.nan`." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Dec-31-2014Dec-31-2015Dec-31-2016Dec-31-2017Dec-31-2018Sep-30-2019
NaNNaNNaNNaNNaNNaN
Revenue364763239854200628237162279332260812
Other RevenueNaN1552NaNNaNNaNNaN
Total Revenue364763241406200628237162279332260812
NaNNaNNaNNaNNaNNaNNaN
\n", "
" ], "text/plain": [ " Dec-31-2014 Dec-31-2015 Dec-31-2016 Dec-31-2017 Dec-31-2018 \\\n", " NaN NaN NaN NaN NaN \n", "Revenue 364763 239854 200628 237162 279332 \n", "Other Revenue NaN 1552 NaN NaN NaN \n", " Total Revenue 364763 241406 200628 237162 279332 \n", "NaN NaN NaN NaN NaN NaN \n", "\n", " Sep-30-2019 \n", " NaN \n", "Revenue 260812 \n", "Other Revenue NaN \n", " Total Revenue 260812 \n", "NaN NaN " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "\n", "inc_df = inc_df.replace('-', np.nan)\n", "inc_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can see that those `-` got filled in for missing values. Now we want to remove the rows which have missing data. We can use `dropna` for this purpose. We must be careful to pass `how='all'` to `dropna` though. The default is `how='any'`, which means the row will be dropped if there is any missing value. Here we only want to drop the row if it is entirely missing, so we will use `how='all'`." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Dec-31-2014Dec-31-2015Dec-31-2016Dec-31-2017Dec-31-2018Sep-30-2019
Revenue364763239854200628237162279332260812
Other RevenueNaN1552NaNNaNNaNNaN
Total Revenue364763241406200628237162279332260812
Cost Of Goods Sold234856163605132759159053190752181228
Gross Profit1299077780167869781098858079584
\n", "
" ], "text/plain": [ " Dec-31-2014 Dec-31-2015 Dec-31-2016 Dec-31-2017 \\\n", "Revenue 364763 239854 200628 237162 \n", "Other Revenue NaN 1552 NaN NaN \n", " Total Revenue 364763 241406 200628 237162 \n", "Cost Of Goods Sold 234856 163605 132759 159053 \n", " Gross Profit 129907 77801 67869 78109 \n", "\n", " Dec-31-2018 Sep-30-2019 \n", "Revenue 279332 260812 \n", "Other Revenue NaN NaN \n", " Total Revenue 279332 260812 \n", "Cost Of Goods Sold 190752 181228 \n", " Gross Profit 88580 79584 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df = inc_df.dropna(how='all')\n", "inc_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can see that it has dropped the rows with all missing values, but not `Other Revenue` which has mostly missing values." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now let's wrap this data cleaning process up into a function, because we're going to want to apply it to the balance sheet data as well." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Dec-31-2014Dec-31-2015Dec-31-2016Dec-31-2017Dec-31-2018Sep-30-2019
Revenue364763239854200628237162279332260812
Other RevenueNaN1552NaNNaNNaNNaN
Total Revenue364763241406200628237162279332260812
Cost Of Goods Sold234856163605132759159053190752181228
Gross Profit1299077780167869781098858079584
\n", "
" ], "text/plain": [ " Dec-31-2014 Dec-31-2015 Dec-31-2016 Dec-31-2017 \\\n", "Revenue 364763 239854 200628 237162 \n", "Other Revenue NaN 1552 NaN NaN \n", " Total Revenue 364763 241406 200628 237162 \n", "Cost Of Goods Sold 234856 163605 132759 159053 \n", " Gross Profit 129907 77801 67869 78109 \n", "\n", " Dec-31-2018 Sep-30-2019 \n", "Revenue 279332 260812 \n", "Other Revenue NaN NaN \n", " Total Revenue 279332 260812 \n", "Cost Of Goods Sold 190752 181228 \n", " Gross Profit 88580 79584 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def load_and_clean_statement_df(statements_path, sheet_name):\n", " df = pd.read_excel(statements_path, sheet_name=sheet_name, index_col=0)\n", " df = df.replace('-', np.nan)\n", " df = df.dropna(how='all')\n", " return df\n", "\n", "inc_df = load_and_clean_statement_df(all_statements_path, 'Income Statement')\n", "inc_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Looks good. Let's use it on the balance sheet." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Balance Sheet" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Since we wrapped all this up in a function, now this process is very easy for the balance sheet." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2014-12-312015-12-312016-12-312017-12-312018-12-312019-09-30
Cash And Equivalents461637053657317730425351
Total Cash & ST Investments461637053657317730425351
Accounts Receivable185411324316033212741963825308
Other Receivables94686632536143235063NaN
Total Receivables280091987521394255972470125308
\n", "
" ], "text/plain": [ " 2014-12-31 2015-12-31 2016-12-31 2017-12-31 \\\n", "Cash And Equivalents 4616 3705 3657 3177 \n", " Total Cash & ST Investments 4616 3705 3657 3177 \n", "Accounts Receivable 18541 13243 16033 21274 \n", "Other Receivables 9468 6632 5361 4323 \n", " Total Receivables 28009 19875 21394 25597 \n", "\n", " 2018-12-31 2019-09-30 \n", "Cash And Equivalents 3042 5351 \n", " Total Cash & ST Investments 3042 5351 \n", "Accounts Receivable 19638 25308 \n", "Other Receivables 5063 NaN \n", " Total Receivables 24701 25308 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bs_df = load_and_clean_statement_df(all_statements_path, 'Balance Sheet')\n", "bs_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Working with the Data - `pandas` Method\n", "\n", "Now we want to calculate free cash flow. As a reminder, here are the steps:\n", "- Calculate non-cash expenses\n", "- Calculate increase in working capital\n", "- Calculate capital expenditures\n", "- Calculate free cash flow from net income and the preceding items" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Calculate Non-Cash Expenses" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For non-cash expenses, we need to total depreciation, amortization, stock-based compensation, impairment charges, and gains/losses on investments.\n", "\n", "To see what we have in the two datasets, we can check the `.index` attribute of the `DataFrame`s." ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Revenue', 'Other Revenue', ' Total Revenue', 'Cost Of Goods Sold',\n", " ' Gross Profit', 'Selling General & Admin Exp.',\n", " 'Exploration/Drilling Costs', 'Depreciation & Amort.',\n", " 'Other Operating Expense/(Income)', ' Other Operating Exp., Total',\n", " ' Operating Income', 'Interest Expense', ' Net Interest Exp.',\n", " 'Income/(Loss) from Affiliates', 'Currency Exchange Gains (Loss)',\n", " 'Other Non-Operating Inc. (Exp.)', ' EBT Excl. Unusual Items',\n", " 'Gain (Loss) On Sale Of Invest.', 'Gain (Loss) On Sale Of Assets',\n", " 'Asset Writedown', ' EBT Incl. Unusual Items', 'Income Tax Expense',\n", " ' Earnings from Cont. Ops.', ' Net Income to Company',\n", " 'Minority Int. in Earnings', ' Net Income',\n", " ' NI to Common Incl Extra Items', ' NI to Common Excl. Extra Items',\n", " 'Basic EPS', 'Basic EPS Excl. Extra Items',\n", " 'Weighted Avg. Basic Shares Out.', 'Diluted EPS',\n", " 'Diluted EPS Excl. Extra Items', 'Weighted Avg. Diluted Shares Out.',\n", " 'Normalized Basic EPS', 'Normalized Diluted EPS', 'Dividends per Share',\n", " 'Payout Ratio %', 'Shares per Depository Receipt', 'EBITDA', 'EBITA',\n", " 'EBIT', 'EBITDAR', 'As Reported Total Revenue*', 'Effective Tax Rate %',\n", " 'Normalized Net Income', 'Interest Capitalized',\n", " 'Non-Cash Pension Expense', 'Filing Date', 'Restatement Type',\n", " 'Calculation Type', 'Stock-Based Comp., Unallocated',\n", " ' Stock-Based Comp., Total'],\n", " dtype='object')" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.index" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Looking like we have depreciation, impairment (called asset writedown here) and gains/losses on investments and assets. There is no stock-based compensation so we will exclude that from the calculation." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To get the entire `Series` of data from the `DataFrame` by the `index` value, we can use `.loc` on the `DataFrame`." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 NaN\n", "Dec-31-2015 NaN\n", "Dec-31-2016 -3600\n", "Dec-31-2017 -2000\n", "Dec-31-2018 -700\n", "Sep-30-2019 -700\n", "Name: Asset Writedown, dtype: object" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.loc['Asset Writedown']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "That gives us each year's impairment. Notice that it's negative though, representing an expense. The calculation assumes it is positive. We can make it positive by using `abs` (absolute value). " ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 NaN\n", "Dec-31-2015 NaN\n", "Dec-31-2016 3600\n", "Dec-31-2017 2000\n", "Dec-31-2018 700\n", "Sep-30-2019 700\n", "Name: Asset Writedown, dtype: object" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "abs(inc_df.loc['Asset Writedown'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can do math directly with `Series`." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 17297\n", "Dec-31-2015 18048\n", "Dec-31-2016 18708\n", "Dec-31-2017 17893\n", "Dec-31-2018 18045\n", "Sep-30-2019 18403\n", "Name: Depreciation & Amort., dtype: object" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.loc['Depreciation & Amort.']" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 NaN\n", "Dec-31-2015 NaN\n", "Dec-31-2016 22308\n", "Dec-31-2017 19893\n", "Dec-31-2018 18745\n", "Sep-30-2019 19103\n", "dtype: object" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.loc['Depreciation & Amort.'] + abs(inc_df.loc['Asset Writedown'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "But what we notice is that if any value is missing, then it will cause the final calculation to be missing. This is not what we want. We want it to assume it is zero if it is missing. So let's fill the `DataFrame`s with zeroes if they are missing. We can do this using `fillna`." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "inc_df = inc_df.fillna(0)\n", "bs_df = bs_df.fillna(0)" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 0\n", "Dec-31-2015 0\n", "Dec-31-2016 3600\n", "Dec-31-2017 2000\n", "Dec-31-2018 700\n", "Sep-30-2019 700\n", "Name: Asset Writedown, dtype: object" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "abs(inc_df.loc['Asset Writedown'])" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 17297\n", "Dec-31-2015 18048\n", "Dec-31-2016 22308\n", "Dec-31-2017 19893\n", "Dec-31-2018 18745\n", "Sep-30-2019 19103\n", "dtype: object" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_df.loc['Depreciation & Amort.'] + abs(inc_df.loc['Asset Writedown'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we are getting the type of result we want. Let's go ahead and do the full calculation." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "non_cash_expenses = (\n", " inc_df.loc['Depreciation & Amort.'] + \n", " abs(inc_df.loc['Asset Writedown']) + \n", " inc_df.loc['Gain (Loss) On Sale Of Invest.'] + \n", " inc_df.loc['Gain (Loss) On Sale Of Assets']\n", ") # NOTE: split onto multiple lines for readability, it would function exactly the same on one line without parentheses" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 20443\n", "Dec-31-2015 18232\n", "Dec-31-2016 23990\n", "Dec-31-2017 20227\n", "Dec-31-2018 20738\n", "Sep-30-2019 21096\n", "dtype: object" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "non_cash_expenses" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Calculate Increase in Working Capital" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "First we need to calculate the net working capial, then calculate the change in that. Here we need balance sheet data, so let's look at the `index` there." ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Cash And Equivalents', ' Total Cash & ST Investments',\n", " 'Accounts Receivable', 'Other Receivables', ' Total Receivables',\n", " 'Inventory', 'Deferred Tax Assets, Curr.', 'Restricted Cash',\n", " 'Other Current Assets', ' Total Current Assets',\n", " 'Gross Property, Plant & Equipment', 'Accumulated Depreciation',\n", " ' Net Property, Plant & Equipment', 'Long-term Investments',\n", " 'Deferred Tax Assets, LT', 'Other Long-Term Assets', 'Total Assets',\n", " 'Accounts Payable', 'Accrued Exp.', 'Short-term Borrowings',\n", " 'Curr. Port. of LT Debt', 'Curr. Port. of Cap. Leases',\n", " 'Curr. Income Taxes Payable', 'Other Current Liabilities',\n", " ' Total Current Liabilities', 'Long-Term Debt', 'Capital Leases',\n", " 'Pension & Other Post-Retire. Benefits',\n", " 'Def. Tax Liability, Non-Curr.', 'Other Non-Current Liabilities',\n", " 'Total Liabilities', 'Common Stock', 'Retained Earnings',\n", " 'Treasury Stock', 'Comprehensive Inc. and Other',\n", " ' Total Common Equity', 'Minority Interest', 'Total Equity',\n", " 'Total Liabilities And Equity', 'Total Shares Out. on Filing Date',\n", " 'Total Shares Out. on Balance Sheet Date', 'Book Value/Share',\n", " 'Tangible Book Value', 'Tangible Book Value/Share', 'Total Debt',\n", " 'Net Debt', 'Debt Equiv. of Unfunded Proj. Benefit Obligation',\n", " 'Debt Equivalent Oper. Leases', 'Total Minority Interest',\n", " 'Equity Method Investments', 'Inventory Method', 'LIFO Reserve',\n", " 'Raw Materials Inventory', 'Finished Goods Inventory',\n", " 'Full Time Employees', 'Accum. Allowance for Doubtful Accts',\n", " 'Filing Date', 'Restatement Type', 'Calculation Type'],\n", " dtype='object')" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bs_df.index" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "nwc = bs_df.loc['Accounts Receivable'] + bs_df.loc['Inventory'] - bs_df.loc['Accounts Payable']" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 9933\n", "2015-12-31 11414\n", "2016-12-31 13312\n", "2017-12-31 16565\n", "2018-12-31 17533\n", "2019-09-30 3562\n", "dtype: object" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nwc" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To get a change, we can take advantage of `Series.shift`, which shifts all the values by a number of rows." ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 9933\n", "2016-12-31 11414\n", "2017-12-31 13312\n", "2018-12-31 16565\n", "2019-09-30 17533\n", "dtype: object" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nwc.shift(1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see that with `shift(1)`, the value from last period is now in this period. So `nwc.shift(1)` is representing last year's net working capital. So then the change in net working capital is simply:" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "change_nwc = nwc - nwc.shift(1)" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 1481\n", "2016-12-31 1898\n", "2017-12-31 3253\n", "2018-12-31 968\n", "2019-09-30 -13971\n", "dtype: object" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "change_nwc" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Calculate Capital Expenditures\n", "\n", "Here we need to first get the change in net property, plant, and equipment, then add the current depreciation to get capital expenditures." ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 252668\n", "2015-12-31 251605\n", "2016-12-31 244224\n", "2017-12-31 252630\n", "2018-12-31 247101\n", "2019-09-30 257065\n", "Name: Net Property, Plant & Equipment, dtype: object" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bs_df.loc[' Net Property, Plant & Equipment']" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "change_ppe = bs_df.loc[' Net Property, Plant & Equipment'] - bs_df.loc[' Net Property, Plant & Equipment'].shift(1)" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 -1063\n", "2016-12-31 -7381\n", "2017-12-31 8406\n", "2018-12-31 -5529\n", "2019-09-30 9964\n", "Name: Net Property, Plant & Equipment, dtype: object" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "change_ppe" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [], "source": [ "capex = change_ppe + inc_df.loc['Depreciation & Amort.']" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 16985\n", "2016-12-31 11327\n", "2017-12-31 26299\n", "2018-12-31 12516\n", "2019-09-30 28367\n", "dtype: object" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "capex" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Put it All Together into FCFs" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": [ "fcf = inc_df.loc[' Net Income'] + non_cash_expenses - change_nwc - capex" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Dec-31-2014 NaN\n", "Dec-31-2015 15916\n", "Dec-31-2016 18605\n", "Dec-31-2017 10385\n", "Dec-31-2018 28094\n", "Sep-30-2019 21350\n", "dtype: object" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "fcf" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Working with the Data - `finstmt` Method\n", "\n", "There are some drawbacks to using plain `pandas` for these kinds of analyses:\n", "- Different financial statements will have different names for items, so code will be tied to only one data provider\n", "- Names are coming in with extra spaces, bad formatting\n", "- From different data providers, some items are reported as negative numbers and others as positive, would need to handle this all for general code\n", "- It's pretty inconvenient using `.loc` with the full row name.\n", "- It's a little verbose to get a change in a variable\n", "- For each variable, we need to think about which financial statement it comes from to be able to pull it, and then look up the appropriate name\n", "- We had to remember to fill in zeroes or the results would not be correct\n", "- Common calculations still need to be done manually (everyone calculates FCF, shouldn't it be easier?)\n", "\n", "Because of these issues, I searched around for a solution to them, but could not find any. But the beauty of open source is anyone can develop a package and make it available to everyone. So I developed the `finstmt` package which handles all of these issues.\n", "\n", "https://nickderobertis.github.io/py-finstmt/\n", "\n", "### An Aside to Package Installation\n", "\n", "To get started using it, we will need to install this package. It doesn't come with Anaconda because I created it! To install packages in Python, the general way is `pip install mypackage` replacing `mypackage` with whatever you want to install. You would run this command inside the `Anaconda Prompt`. An alternative is to run it through Jupyter. If you put `!` before a command in Jupyter, Jupyter interprets that as you wanting to run that on the command line and not in the Jupyter notebook. Let's see that here." ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: finstmt in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (0.2.2)\n", "Requirement already satisfied: sympy in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from finstmt) (1.5.1)\n", "Requirement already satisfied: xlrd in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from finstmt) (1.2.0)\n", "Requirement already satisfied: matplotlib in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from finstmt) (3.2.0rc3)\n", "Requirement already satisfied: pandas in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from finstmt) (1.0.1)\n", "Requirement already satisfied: mpmath>=0.19 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from sympy->finstmt) (1.1.0)\n", "Requirement already satisfied: python-dateutil>=2.1 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from matplotlib->finstmt) (2.8.1)\n", "Requirement already satisfied: kiwisolver>=1.0.1 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from matplotlib->finstmt) (1.1.0)\n", "Requirement already satisfied: numpy>=1.11 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from matplotlib->finstmt) (1.18.1)\n", "Requirement already satisfied: cycler>=0.10 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from matplotlib->finstmt) (0.10.0)\n", "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from matplotlib->finstmt) (2.4.6)\n", "Requirement already satisfied: pytz>=2017.2 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from pandas->finstmt) (2019.3)\n", "Requirement already satisfied: six>=1.5 in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from python-dateutil>=2.1->matplotlib->finstmt) (1.14.0)\n", "Requirement already satisfied: setuptools in /home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages (from kiwisolver>=1.0.1->matplotlib->finstmt) (41.4.0)\n", "\u001b[33mWARNING: You are using pip version 19.3.1; however, version 20.0.2 is available.\n", "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n" ] } ], "source": [ "!pip install finstmt" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You should see that type of output, and you should especially see `Successfully installed finstmt` to see that it worked. Now we are able to `import finstmt`. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using `finstmt`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "I have created three main classes (so far) to help working with the data. `IncomeStatements`, `BalanceSheets`, and `FinancialStatements`. Let's import them." ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [], "source": [ "from finstmt import IncomeStatements, BalanceSheets, FinancialStatements" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`finstmt` expects to receive your data with missing rows already removed, with data items as the index, and with dates as the columns. It is not necessary to fill in zeroes or take absolute values as those are handled by the package. The output of our `load_and_clean_statement_df` function in the beginning puts it in the perfect format for this. Let's reload the `DataFrame`s so that we haven't messed with them." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [], "source": [ "inc_df = load_and_clean_statement_df(all_statements_path, 'Income Statement')\n", "bs_df = load_and_clean_statement_df(all_statements_path, 'Balance Sheet')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we want to create `IncomeStatements` from the income statements and `BalanceSheets` from the balance sheets. The way to do this is using `.from_df` methods of each." ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/nick/.local/share/virtualenvs/fin-model-course-eIFMSc8A/lib/python3.7/site-packages/finstmt/findata/database.py:77: UserWarning: Previously had revenue extracted from \"Revenue\". Replacing with value from \" Total Revenue\"\n", " warnings.warn(f'Previously had {item_config.key} '\n" ] } ], "source": [ "inc_data = IncomeStatements.from_df(inc_df)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The first feature that we get from this package is much cleaner output out of the box. Simply display the data and it has cleaned up all the names of the variables, standardized the date format, and formatted values in currency format." ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/201512/31/201612/31/201712/31/201809/30/2019
Revenue$364,763$241,406$200,628$237,162$279,332$260,812
Cost of Goods Sold$234,856$163,605$132,759$159,053$190,752$181,228
Gross Profit$129,907$77,801$67,869$78,109$88,580$79,584
R&D Expense------
SG&A Expense$12,002$10,961$11,783$11,893$12,300$12,094
Depreciation & Amortization Expense$17,297$18,048$18,708$17,893$18,045$18,403
Other Operating Expenses$64,857$32,834$31,375$32,459$35,230$33,161
Operating Expense$94,156$61,843$61,866$62,245$65,575$63,658
Earnings Before Interest and Taxes$34,082$14,435$4,536$14,074$21,539$14,459
Interest Expense$286$311$453$601$766$844
Gain on Sale of Investments$-5$-42----
Gain on Sale of Assets$3,151$226$1,682$334$1,993$1,993
Impairment Expense--$3,600$2,000$700$700
Earnings Before Tax$51,630$21,966$7,969$18,674$30,953$21,763
Income Tax Expense$18,015$5,415$406$1,174$9,532$6,513
Net Income$32,520$16,150$7,840$19,710$20,840$14,650
\n", "
" ], "text/plain": [ "IncomeStatements(statements={Timestamp('2014-12-31 00:00:00'): IncomeStatementData(revenue=364763, cogs=234856, sga=12002, int_exp=286, tax_exp=18015, rd_exp=0, dep_exp=17297, other_op_exp=64857, gain_on_sale_invest=-5, gain_on_sale_asset=3151, impairment=nan, op_exp=94156.0, ebit=34082, ebt=51630, net_income=32520), Timestamp('2015-12-31 00:00:00'): IncomeStatementData(revenue=241406, cogs=163605, sga=10961, int_exp=311, tax_exp=5415, rd_exp=0, dep_exp=18048, other_op_exp=32834, gain_on_sale_invest=-42, gain_on_sale_asset=226, impairment=nan, op_exp=61843.0, ebit=14435, ebt=21966, net_income=16150), Timestamp('2016-12-31 00:00:00'): IncomeStatementData(revenue=200628, cogs=132759, sga=11783, int_exp=453, tax_exp=406, rd_exp=0, dep_exp=18708, other_op_exp=31375, gain_on_sale_invest=nan, gain_on_sale_asset=1682, impairment=3600, op_exp=61866.0, ebit=4536, ebt=7969, net_income=7840), Timestamp('2017-12-31 00:00:00'): IncomeStatementData(revenue=237162, cogs=159053, sga=11893, int_exp=601, tax_exp=1174, rd_exp=0, dep_exp=17893, other_op_exp=32459, gain_on_sale_invest=nan, gain_on_sale_asset=334, impairment=2000, op_exp=62245.0, ebit=14074, ebt=18674, net_income=19710), Timestamp('2018-12-31 00:00:00'): IncomeStatementData(revenue=279332, cogs=190752, sga=12300, int_exp=766, tax_exp=9532, rd_exp=0, dep_exp=18045, other_op_exp=35230, gain_on_sale_invest=nan, gain_on_sale_asset=1993, impairment=700, op_exp=65575.0, ebit=21539, ebt=30953, net_income=20840), Timestamp('2019-09-30 00:00:00'): IncomeStatementData(revenue=260812, cogs=181228, sga=12094, int_exp=844, tax_exp=6513, rd_exp=0, dep_exp=18403, other_op_exp=33161, gain_on_sale_invest=nan, gain_on_sale_asset=1993, impairment=700, op_exp=63658.0, ebit=14459, ebt=21763, net_income=14650)})" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also see that it has made the impairment expense (called asset writedown before) into a positive number automatically.\n", "\n", "The next main feature is easier access to variables. They have short names you can access via `.` and then the name. You can tab-complete all of these names. Go into the next cell, put your cursor after the `.`, and press tab. You might have to do it a couple times to get it to come up, but you will see all the variables." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "inc_data." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can access those variables easily." ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 364763\n", "2015-12-31 241406\n", "2016-12-31 200628\n", "2017-12-31 237162\n", "2018-12-31 279332\n", "2019-09-30 260812\n", "dtype: int64" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_data.revenue" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can also see we get back to the raw numbers when accessing a variable, even though when we show the full statement, it is formatted.\n", "\n", "We can also pull out one or more dates from the statements easily. Notice also that I'm not even using the same format of the dates, it understands that what you are passing is a date and converts it to match the column." ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0
Revenue$364,763
Cost of Goods Sold$234,856
Gross Profit$129,907
R&D Expense-
SG&A Expense$12,002
Depreciation & Amortization Expense$17,297
Other Operating Expenses$64,857
Operating Expense$94,156
Earnings Before Interest and Taxes$34,082
Interest Expense$286
Gain on Sale of Investments$-5
Gain on Sale of Assets$3,151
Impairment Expense-
Earnings Before Tax$51,630
Income Tax Expense$18,015
Net Income$32,520
\n", "
" ], "text/plain": [ "IncomeStatementData(revenue=364763, cogs=234856, sga=12002, int_exp=286, tax_exp=18015, rd_exp=0, dep_exp=17297, other_op_exp=64857, gain_on_sale_invest=-5, gain_on_sale_asset=3151, impairment=nan, op_exp=94156.0, ebit=34082, ebt=51630, net_income=32520)" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_data['2014-12-31']" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/2015
Revenue$364,763$241,406
Cost of Goods Sold$234,856$163,605
Gross Profit$129,907$77,801
R&D Expense--
SG&A Expense$12,002$10,961
Depreciation & Amortization Expense--
Other Operating Expenses$64,857$32,834
Operating Expense$94,156$61,843
Earnings Before Interest and Taxes$34,082$14,435
Interest Expense$286$311
Gain on Sale of Investments$-5$-42
Gain on Sale of Assets$3,151$226
Impairment Expense--
Earnings Before Tax$51,630$21,966
Income Tax Expense$18,015$5,415
Net Income$32,520$16,150
\n", "
" ], "text/plain": [ "IncomeStatements(statements={Timestamp('2014-12-31 00:00:00'): IncomeStatementData(revenue=364763.0, cogs=234856.0, sga=12002.0, int_exp=286.0, tax_exp=18015.0, rd_exp=0.0, dep_exp=0, other_op_exp=64857.0, gain_on_sale_invest=-5.0, gain_on_sale_asset=3151.0, impairment=0.0, op_exp=94156.0, ebit=34082.0, ebt=51630.0, net_income=32520.0), Timestamp('2015-12-31 00:00:00'): IncomeStatementData(revenue=241406.0, cogs=163605.0, sga=10961.0, int_exp=311.0, tax_exp=5415.0, rd_exp=0.0, dep_exp=0, other_op_exp=32834.0, gain_on_sale_invest=-42.0, gain_on_sale_asset=226.0, impairment=0.0, op_exp=61843.0, ebit=14435.0, ebt=21966.0, net_income=16150.0)})" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "inc_data[['2014-12-31', '2015-12-31']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now let's go ahead and construct the balance sheet." ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [], "source": [ "bs_data = BalanceSheets.from_df(bs_df)" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/201512/31/201612/31/201712/31/201809/30/2019
Cash and Cash Equivalents$4,616$3,705$3,657$3,177$3,042$5,351
Short-Term Investments------
Cash and Short-Term Investments$4,616$3,705$3,657$3,177$3,042$5,351
Receivables$18,541$13,243$16,033$21,274$19,638$25,308
Inventory$16,678$16,245$15,080$16,992$18,958$17,590
Deferred Tax Assets, Current$2,001$1,329----
Other Current Assets$1,564$1,469$1,285$1,368$1,272$1,759
Total Current Assets$52,910$42,623$41,416$47,134$47,973$50,008
Grosss Property, Plant & Equipment$446,789$447,337$453,915$477,185$477,190-
Accumulated Depreciation$194,121$195,732$209,691$224,555$230,089-
Net Property, Plant & Equipment$252,668$251,605$244,224$252,630$247,101$257,065
Goodwill and Intangible Assets------
Long-Term Investments$20,543$20,611$20,964$24,528$26,592$34,527
Deferred Tax Assets, Long-Term$3,955$3,421$4,120$3,318$3,209-
Other Long-Term Assets$19,417$18,498$19,590$21,081$21,321$17,761
Total Non-Current Assets$296,583$294,135$288,898$301,557$298,223-
Total Assets$349,493$336,758$330,314$348,691$346,196$359,361
Payables$25,286$18,074$17,801$21,701$21,063$39,336
Short-Term Debt$16,698$18,204$10,870$13,164$13,188$21,027
Current Portion of Long-Term Debt$770$558$2,960$4,766$4,070-
Tax Liabilities, Short-Term$39,230$36,818$34,041$26,893$27,244$26,513
Other Current Liabilities$13,651$11,401$10,739$11,784$12,925-
Total Current Liabilities$64,633$53,976$47,638$57,771$57,138$64,195
Long-Term Debt$11,278$18,687$27,707$23,079$19,235$24,669
Total Debt$29,121$38,687$42,762$42,336$37,796$52,887
Deferred Revenue------
Tax Liabilities, Long-Term$39,230$36,818$34,041$26,893$27,244$26,513
Deposit Liabilities------
Other Long-Term Liabilities$27,111$26,582$25,193$23,989$22,476$21,693
Total Non-Current Liabilities$77,619$82,087$86,941$73,961$68,955$72,875
Total Liabilities$168,429$159,948$156,484$154,191$147,668$162,252
Common Stock$10,792$11,612$12,157$14,656$15,258$15,795
Other Comprehensive Income$-18,957$-23,511$-22,239$-16,262$-19,564$-19,277
Retained Earnings$408,384$412,444$407,831$414,540$421,653$419,367
Minority Interest$6,665$5,999$6,505$6,812$6,734$7,194
Total Stockholder's Equity$181,064$176,810$173,830$194,500$198,528$197,109
Total Liabilities and Equity$349,493$336,758$330,314$348,691$346,196$359,361
\n", "
" ], "text/plain": [ "BalanceSheets(statements={Timestamp('2014-12-31 00:00:00'): BalanceSheetData(cash=4616, st_invest=0, receivables=18541, inventory=16678, lt_invest=20543, def_tax_st=2001, other_current_assets=1564, gross_ppe=446789, dep=194121, goodwill=0, def_tax_lt=3955, other_lt_assets=19417, payables=25286, current_lt_debt=770, st_debt=16698, lt_debt=11278, deferred_rev=0, tax_liab_st=39230, other_current_liab=13651, tax_liab_lt=39230, deposit_liab=0, other_lt_liab=27111, common_stock=10792, minority_interest=6665, other_income=-18957, retained_earnings=408384, cash_and_st_invest=4616, total_current_assets=52910, net_ppe=252668, total_non_current_assets=296583.0, total_assets=349493, total_current_liab=64633, total_debt=29121, total_non_current_liab=77619.0, total_liab=168429, total_equity=181064, total_liab_and_equity=349493), Timestamp('2015-12-31 00:00:00'): BalanceSheetData(cash=3705, st_invest=0, receivables=13243, inventory=16245, lt_invest=20611, def_tax_st=1329, other_current_assets=1469, gross_ppe=447337, dep=195732, goodwill=0, def_tax_lt=3421, other_lt_assets=18498, payables=18074, current_lt_debt=558, st_debt=18204, lt_debt=18687, deferred_rev=0, tax_liab_st=36818, other_current_liab=11401, tax_liab_lt=36818, deposit_liab=0, other_lt_liab=26582, common_stock=11612, minority_interest=5999, other_income=-23511, retained_earnings=412444, cash_and_st_invest=3705, total_current_assets=42623, net_ppe=251605, total_non_current_assets=294135.0, total_assets=336758, total_current_liab=53976, total_debt=38687, total_non_current_liab=82087.0, total_liab=159948, total_equity=176810, total_liab_and_equity=336758), Timestamp('2016-12-31 00:00:00'): BalanceSheetData(cash=3657, st_invest=0, receivables=16033, inventory=15080, lt_invest=20964, def_tax_st=nan, other_current_assets=1285, gross_ppe=453915, dep=209691, goodwill=0, def_tax_lt=4120, other_lt_assets=19590, payables=17801, current_lt_debt=2960, st_debt=10870, lt_debt=27707, deferred_rev=0, tax_liab_st=34041, other_current_liab=10739, tax_liab_lt=34041, deposit_liab=0, other_lt_liab=25193, common_stock=12157, minority_interest=6505, other_income=-22239, retained_earnings=407831, cash_and_st_invest=3657, total_current_assets=41416, net_ppe=244224, total_non_current_assets=288898.0, total_assets=330314, total_current_liab=47638, total_debt=42762, total_non_current_liab=86941.0, total_liab=156484, total_equity=173830, total_liab_and_equity=330314), Timestamp('2017-12-31 00:00:00'): BalanceSheetData(cash=3177, st_invest=0, receivables=21274, inventory=16992, lt_invest=24528, def_tax_st=nan, other_current_assets=1368, gross_ppe=477185, dep=224555, goodwill=0, def_tax_lt=3318, other_lt_assets=21081, payables=21701, current_lt_debt=4766, st_debt=13164, lt_debt=23079, deferred_rev=0, tax_liab_st=26893, other_current_liab=11784, tax_liab_lt=26893, deposit_liab=0, other_lt_liab=23989, common_stock=14656, minority_interest=6812, other_income=-16262, retained_earnings=414540, cash_and_st_invest=3177, total_current_assets=47134, net_ppe=252630, total_non_current_assets=301557.0, total_assets=348691, total_current_liab=57771, total_debt=42336, total_non_current_liab=73961.0, total_liab=154191, total_equity=194500, total_liab_and_equity=348691), Timestamp('2018-12-31 00:00:00'): BalanceSheetData(cash=3042, st_invest=0, receivables=19638, inventory=18958, lt_invest=26592, def_tax_st=nan, other_current_assets=1272, gross_ppe=477190, dep=230089, goodwill=0, def_tax_lt=3209, other_lt_assets=21321, payables=21063, current_lt_debt=4070, st_debt=13188, lt_debt=19235, deferred_rev=0, tax_liab_st=27244, other_current_liab=12925, tax_liab_lt=27244, deposit_liab=0, other_lt_liab=22476, common_stock=15258, minority_interest=6734, other_income=-19564, retained_earnings=421653, cash_and_st_invest=3042, total_current_assets=47973, net_ppe=247101, total_non_current_assets=298223.0, total_assets=346196, total_current_liab=57138, total_debt=37796, total_non_current_liab=68955.0, total_liab=147668, total_equity=198528, total_liab_and_equity=346196), Timestamp('2019-09-30 00:00:00'): BalanceSheetData(cash=5351, st_invest=0, receivables=25308, inventory=17590, lt_invest=34527, def_tax_st=nan, other_current_assets=1759, gross_ppe=nan, dep=nan, goodwill=0, def_tax_lt=nan, other_lt_assets=17761, payables=39336, current_lt_debt=nan, st_debt=21027, lt_debt=24669, deferred_rev=0, tax_liab_st=26513, other_current_liab=nan, tax_liab_lt=26513, deposit_liab=0, other_lt_liab=21693, common_stock=15795, minority_interest=7194, other_income=-19277, retained_earnings=419367, cash_and_st_invest=5351, total_current_assets=50008, net_ppe=257065, total_non_current_assets=nan, total_assets=359361, total_current_liab=64195, total_debt=52887, total_non_current_liab=72875.0, total_liab=162252, total_equity=197109, total_liab_and_equity=359361)})" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bs_data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can work with the balance sheet in the same way as the income statement.\n", "\n", "The package gets even more powerful when we combine the statements." ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [], "source": [ "stmts = FinancialStatements(inc_data, bs_data)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We nice formatting of the statement, showing each nicely formatted statement with headers." ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "

Income Statement

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/201512/31/201612/31/201712/31/201809/30/2019
Revenue$364,763$241,406$200,628$237,162$279,332$260,812
Cost of Goods Sold$234,856$163,605$132,759$159,053$190,752$181,228
Gross Profit$129,907$77,801$67,869$78,109$88,580$79,584
R&D Expense------
SG&A Expense$12,002$10,961$11,783$11,893$12,300$12,094
Depreciation & Amortization Expense$17,297$18,048$18,708$17,893$18,045$18,403
Other Operating Expenses$64,857$32,834$31,375$32,459$35,230$33,161
Operating Expense$94,156$61,843$61,866$62,245$65,575$63,658
Earnings Before Interest and Taxes$34,082$14,435$4,536$14,074$21,539$14,459
Interest Expense$286$311$453$601$766$844
Gain on Sale of Investments$-5$-42----
Gain on Sale of Assets$3,151$226$1,682$334$1,993$1,993
Impairment Expense--$3,600$2,000$700$700
Earnings Before Tax$51,630$21,966$7,969$18,674$30,953$21,763
Income Tax Expense$18,015$5,415$406$1,174$9,532$6,513
Net Income$32,520$16,150$7,840$19,710$20,840$14,650
\n", "
\n", "

Balance Sheet

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/201512/31/201612/31/201712/31/201809/30/2019
Cash and Cash Equivalents$4,616$3,705$3,657$3,177$3,042$5,351
Short-Term Investments------
Cash and Short-Term Investments$4,616$3,705$3,657$3,177$3,042$5,351
Receivables$18,541$13,243$16,033$21,274$19,638$25,308
Inventory$16,678$16,245$15,080$16,992$18,958$17,590
Deferred Tax Assets, Current$2,001$1,329----
Other Current Assets$1,564$1,469$1,285$1,368$1,272$1,759
Total Current Assets$52,910$42,623$41,416$47,134$47,973$50,008
Grosss Property, Plant & Equipment$446,789$447,337$453,915$477,185$477,190-
Accumulated Depreciation$194,121$195,732$209,691$224,555$230,089-
Net Property, Plant & Equipment$252,668$251,605$244,224$252,630$247,101$257,065
Goodwill and Intangible Assets------
Long-Term Investments$20,543$20,611$20,964$24,528$26,592$34,527
Deferred Tax Assets, Long-Term$3,955$3,421$4,120$3,318$3,209-
Other Long-Term Assets$19,417$18,498$19,590$21,081$21,321$17,761
Total Non-Current Assets$296,583$294,135$288,898$301,557$298,223-
Total Assets$349,493$336,758$330,314$348,691$346,196$359,361
Payables$25,286$18,074$17,801$21,701$21,063$39,336
Short-Term Debt$16,698$18,204$10,870$13,164$13,188$21,027
Current Portion of Long-Term Debt$770$558$2,960$4,766$4,070-
Tax Liabilities, Short-Term$39,230$36,818$34,041$26,893$27,244$26,513
Other Current Liabilities$13,651$11,401$10,739$11,784$12,925-
Total Current Liabilities$64,633$53,976$47,638$57,771$57,138$64,195
Long-Term Debt$11,278$18,687$27,707$23,079$19,235$24,669
Total Debt$29,121$38,687$42,762$42,336$37,796$52,887
Deferred Revenue------
Tax Liabilities, Long-Term$39,230$36,818$34,041$26,893$27,244$26,513
Deposit Liabilities------
Other Long-Term Liabilities$27,111$26,582$25,193$23,989$22,476$21,693
Total Non-Current Liabilities$77,619$82,087$86,941$73,961$68,955$72,875
Total Liabilities$168,429$159,948$156,484$154,191$147,668$162,252
Common Stock$10,792$11,612$12,157$14,656$15,258$15,795
Other Comprehensive Income$-18,957$-23,511$-22,239$-16,262$-19,564$-19,277
Retained Earnings$408,384$412,444$407,831$414,540$421,653$419,367
Minority Interest$6,665$5,999$6,505$6,812$6,734$7,194
Total Stockholder's Equity$181,064$176,810$173,830$194,500$198,528$197,109
Total Liabilities and Equity$349,493$336,758$330,314$348,691$346,196$359,361
\n", "
\n", " " ], "text/plain": [ "FinancialStatements(income_statements=IncomeStatements(statements={Timestamp('2014-12-31 00:00:00'): IncomeStatementData(revenue=364763, cogs=234856, sga=12002, int_exp=286, tax_exp=18015, rd_exp=0, dep_exp=17297, other_op_exp=64857, gain_on_sale_invest=-5, gain_on_sale_asset=3151, impairment=nan, op_exp=94156.0, ebit=34082, ebt=51630, net_income=32520), Timestamp('2015-12-31 00:00:00'): IncomeStatementData(revenue=241406, cogs=163605, sga=10961, int_exp=311, tax_exp=5415, rd_exp=0, dep_exp=18048, other_op_exp=32834, gain_on_sale_invest=-42, gain_on_sale_asset=226, impairment=nan, op_exp=61843.0, ebit=14435, ebt=21966, net_income=16150), Timestamp('2016-12-31 00:00:00'): IncomeStatementData(revenue=200628, cogs=132759, sga=11783, int_exp=453, tax_exp=406, rd_exp=0, dep_exp=18708, other_op_exp=31375, gain_on_sale_invest=nan, gain_on_sale_asset=1682, impairment=3600, op_exp=61866.0, ebit=4536, ebt=7969, net_income=7840), Timestamp('2017-12-31 00:00:00'): IncomeStatementData(revenue=237162, cogs=159053, sga=11893, int_exp=601, tax_exp=1174, rd_exp=0, dep_exp=17893, other_op_exp=32459, gain_on_sale_invest=nan, gain_on_sale_asset=334, impairment=2000, op_exp=62245.0, ebit=14074, ebt=18674, net_income=19710), Timestamp('2018-12-31 00:00:00'): IncomeStatementData(revenue=279332, cogs=190752, sga=12300, int_exp=766, tax_exp=9532, rd_exp=0, dep_exp=18045, other_op_exp=35230, gain_on_sale_invest=nan, gain_on_sale_asset=1993, impairment=700, op_exp=65575.0, ebit=21539, ebt=30953, net_income=20840), Timestamp('2019-09-30 00:00:00'): IncomeStatementData(revenue=260812, cogs=181228, sga=12094, int_exp=844, tax_exp=6513, rd_exp=0, dep_exp=18403, other_op_exp=33161, gain_on_sale_invest=nan, gain_on_sale_asset=1993, impairment=700, op_exp=63658.0, ebit=14459, ebt=21763, net_income=14650)}), balance_sheets=BalanceSheets(statements={Timestamp('2014-12-31 00:00:00'): BalanceSheetData(cash=4616, st_invest=0, receivables=18541, inventory=16678, lt_invest=20543, def_tax_st=2001, other_current_assets=1564, gross_ppe=446789, dep=194121, goodwill=0, def_tax_lt=3955, other_lt_assets=19417, payables=25286, current_lt_debt=770, st_debt=16698, lt_debt=11278, deferred_rev=0, tax_liab_st=39230, other_current_liab=13651, tax_liab_lt=39230, deposit_liab=0, other_lt_liab=27111, common_stock=10792, minority_interest=6665, other_income=-18957, retained_earnings=408384, cash_and_st_invest=4616, total_current_assets=52910, net_ppe=252668, total_non_current_assets=296583.0, total_assets=349493, total_current_liab=64633, total_debt=29121, total_non_current_liab=77619.0, total_liab=168429, total_equity=181064, total_liab_and_equity=349493), Timestamp('2015-12-31 00:00:00'): BalanceSheetData(cash=3705, st_invest=0, receivables=13243, inventory=16245, lt_invest=20611, def_tax_st=1329, other_current_assets=1469, gross_ppe=447337, dep=195732, goodwill=0, def_tax_lt=3421, other_lt_assets=18498, payables=18074, current_lt_debt=558, st_debt=18204, lt_debt=18687, deferred_rev=0, tax_liab_st=36818, other_current_liab=11401, tax_liab_lt=36818, deposit_liab=0, other_lt_liab=26582, common_stock=11612, minority_interest=5999, other_income=-23511, retained_earnings=412444, cash_and_st_invest=3705, total_current_assets=42623, net_ppe=251605, total_non_current_assets=294135.0, total_assets=336758, total_current_liab=53976, total_debt=38687, total_non_current_liab=82087.0, total_liab=159948, total_equity=176810, total_liab_and_equity=336758), Timestamp('2016-12-31 00:00:00'): BalanceSheetData(cash=3657, st_invest=0, receivables=16033, inventory=15080, lt_invest=20964, def_tax_st=nan, other_current_assets=1285, gross_ppe=453915, dep=209691, goodwill=0, def_tax_lt=4120, other_lt_assets=19590, payables=17801, current_lt_debt=2960, st_debt=10870, lt_debt=27707, deferred_rev=0, tax_liab_st=34041, other_current_liab=10739, tax_liab_lt=34041, deposit_liab=0, other_lt_liab=25193, common_stock=12157, minority_interest=6505, other_income=-22239, retained_earnings=407831, cash_and_st_invest=3657, total_current_assets=41416, net_ppe=244224, total_non_current_assets=288898.0, total_assets=330314, total_current_liab=47638, total_debt=42762, total_non_current_liab=86941.0, total_liab=156484, total_equity=173830, total_liab_and_equity=330314), Timestamp('2017-12-31 00:00:00'): BalanceSheetData(cash=3177, st_invest=0, receivables=21274, inventory=16992, lt_invest=24528, def_tax_st=nan, other_current_assets=1368, gross_ppe=477185, dep=224555, goodwill=0, def_tax_lt=3318, other_lt_assets=21081, payables=21701, current_lt_debt=4766, st_debt=13164, lt_debt=23079, deferred_rev=0, tax_liab_st=26893, other_current_liab=11784, tax_liab_lt=26893, deposit_liab=0, other_lt_liab=23989, common_stock=14656, minority_interest=6812, other_income=-16262, retained_earnings=414540, cash_and_st_invest=3177, total_current_assets=47134, net_ppe=252630, total_non_current_assets=301557.0, total_assets=348691, total_current_liab=57771, total_debt=42336, total_non_current_liab=73961.0, total_liab=154191, total_equity=194500, total_liab_and_equity=348691), Timestamp('2018-12-31 00:00:00'): BalanceSheetData(cash=3042, st_invest=0, receivables=19638, inventory=18958, lt_invest=26592, def_tax_st=nan, other_current_assets=1272, gross_ppe=477190, dep=230089, goodwill=0, def_tax_lt=3209, other_lt_assets=21321, payables=21063, current_lt_debt=4070, st_debt=13188, lt_debt=19235, deferred_rev=0, tax_liab_st=27244, other_current_liab=12925, tax_liab_lt=27244, deposit_liab=0, other_lt_liab=22476, common_stock=15258, minority_interest=6734, other_income=-19564, retained_earnings=421653, cash_and_st_invest=3042, total_current_assets=47973, net_ppe=247101, total_non_current_assets=298223.0, total_assets=346196, total_current_liab=57138, total_debt=37796, total_non_current_liab=68955.0, total_liab=147668, total_equity=198528, total_liab_and_equity=346196), Timestamp('2019-09-30 00:00:00'): BalanceSheetData(cash=5351, st_invest=0, receivables=25308, inventory=17590, lt_invest=34527, def_tax_st=nan, other_current_assets=1759, gross_ppe=nan, dep=nan, goodwill=0, def_tax_lt=nan, other_lt_assets=17761, payables=39336, current_lt_debt=nan, st_debt=21027, lt_debt=24669, deferred_rev=0, tax_liab_st=26513, other_current_liab=nan, tax_liab_lt=26513, deposit_liab=0, other_lt_liab=21693, common_stock=15795, minority_interest=7194, other_income=-19277, retained_earnings=419367, cash_and_st_invest=5351, total_current_assets=50008, net_ppe=257065, total_non_current_assets=nan, total_assets=359361, total_current_liab=64195, total_debt=52887, total_non_current_liab=72875.0, total_liab=162252, total_equity=197109, total_liab_and_equity=359361)}), forecasts={})" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Variable access becomes even easier, because now you have access to everything from both the income statement and balance sheet, without worrying about where it came from." ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 4616\n", "2015-12-31 3705\n", "2016-12-31 3657\n", "2017-12-31 3177\n", "2018-12-31 3042\n", "2019-09-30 5351\n", "dtype: int64" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.cash" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 234856\n", "2015-12-31 163605\n", "2016-12-31 132759\n", "2017-12-31 159053\n", "2018-12-31 190752\n", "2019-09-30 181228\n", "dtype: int64" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.cogs" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also still access individual dates." ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "

Income Statement

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/2014
Revenue$364,763
Cost of Goods Sold$234,856
Gross Profit$129,907
R&D Expense-
SG&A Expense$12,002
Depreciation & Amortization Expense$17,297
Other Operating Expenses$64,857
Operating Expense$94,156
Earnings Before Interest and Taxes$34,082
Interest Expense$286
Gain on Sale of Investments$-5
Gain on Sale of Assets$3,151
Impairment Expense-
Earnings Before Tax$51,630
Income Tax Expense$18,015
Net Income$32,520
\n", "
\n", "

Balance Sheet

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/2014
Cash and Cash Equivalents$4,616
Short-Term Investments-
Cash and Short-Term Investments$4,616
Receivables$18,541
Inventory$16,678
Deferred Tax Assets, Current$2,001
Other Current Assets$1,564
Total Current Assets$52,910
Grosss Property, Plant & Equipment$446,789
Accumulated Depreciation$194,121
Net Property, Plant & Equipment$252,668
Goodwill and Intangible Assets-
Long-Term Investments$20,543
Deferred Tax Assets, Long-Term$3,955
Other Long-Term Assets$19,417
Total Non-Current Assets$296,583
Total Assets$349,493
Payables$25,286
Short-Term Debt$16,698
Current Portion of Long-Term Debt$770
Tax Liabilities, Short-Term$39,230
Other Current Liabilities$13,651
Total Current Liabilities$64,633
Long-Term Debt$11,278
Total Debt$29,121
Deferred Revenue-
Tax Liabilities, Long-Term$39,230
Deposit Liabilities-
Other Long-Term Liabilities$27,111
Total Non-Current Liabilities$77,619
Total Liabilities$168,429
Common Stock$10,792
Other Comprehensive Income$-18,957
Retained Earnings$408,384
Minority Interest$6,665
Total Stockholder's Equity$181,064
Total Liabilities and Equity$349,493
\n", "
\n", " " ], "text/plain": [ "FinancialStatements(income_statements=IncomeStatements(statements={Timestamp('2014-12-31 00:00:00'): IncomeStatementData(revenue=364763, cogs=234856, sga=12002, int_exp=286, tax_exp=18015, rd_exp=0, dep_exp=17297, other_op_exp=64857, gain_on_sale_invest=-5, gain_on_sale_asset=3151, impairment=nan, op_exp=94156.0, ebit=34082, ebt=51630, net_income=32520)}), balance_sheets=BalanceSheets(statements={Timestamp('2014-12-31 00:00:00'): BalanceSheetData(cash=4616, st_invest=0, receivables=18541, inventory=16678, lt_invest=20543, def_tax_st=2001, other_current_assets=1564, gross_ppe=446789, dep=194121, goodwill=0, def_tax_lt=3955, other_lt_assets=19417, payables=25286, current_lt_debt=770, st_debt=16698, lt_debt=11278, deferred_rev=0, tax_liab_st=39230, other_current_liab=13651, tax_liab_lt=39230, deposit_liab=0, other_lt_liab=27111, common_stock=10792, minority_interest=6665, other_income=-18957, retained_earnings=408384, cash_and_st_invest=4616, total_current_assets=52910, net_ppe=252668, total_non_current_assets=296583.0, total_assets=349493, total_current_liab=64633, total_debt=29121, total_non_current_liab=77619.0, total_liab=168429, total_equity=181064, total_liab_and_equity=349493)}), forecasts={})" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts['2014-12-31']" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "

Income Statement

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/2015
Revenue$364,763$241,406
Cost of Goods Sold$234,856$163,605
Gross Profit$129,907$77,801
R&D Expense--
SG&A Expense$12,002$10,961
Depreciation & Amortization Expense--
Other Operating Expenses$64,857$32,834
Operating Expense$94,156$61,843
Earnings Before Interest and Taxes$34,082$14,435
Interest Expense$286$311
Gain on Sale of Investments$-5$-42
Gain on Sale of Assets$3,151$226
Impairment Expense--
Earnings Before Tax$51,630$21,966
Income Tax Expense$18,015$5,415
Net Income$32,520$16,150
\n", "
\n", "

Balance Sheet

\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
12/31/201412/31/2015
Cash and Cash Equivalents$4,616$3,705
Short-Term Investments--
Cash and Short-Term Investments$4,616$3,705
Receivables$18,541$13,243
Inventory$16,678$16,245
Deferred Tax Assets, Current$2,001$1,329
Other Current Assets$1,564$1,469
Total Current Assets$52,910$42,623
Grosss Property, Plant & Equipment--
Accumulated Depreciation$194,121$195,732
Net Property, Plant & Equipment$252,668$251,605
Goodwill and Intangible Assets--
Long-Term Investments$20,543$20,611
Deferred Tax Assets, Long-Term$3,955$3,421
Other Long-Term Assets$19,417$18,498
Total Non-Current Assets$296,583$294,135
Total Assets$349,493$336,758
Payables$25,286$18,074
Short-Term Debt$16,698$18,204
Current Portion of Long-Term Debt$770$558
Tax Liabilities, Short-Term$39,230$36,818
Other Current Liabilities$13,651$11,401
Total Current Liabilities$64,633$53,976
Long-Term Debt$11,278$18,687
Total Debt$29,121$38,687
Deferred Revenue--
Tax Liabilities, Long-Term$39,230$36,818
Deposit Liabilities--
Other Long-Term Liabilities$27,111$26,582
Total Non-Current Liabilities$77,619$82,087
Total Liabilities$168,429$159,948
Common Stock$10,792$11,612
Other Comprehensive Income$-18,957$-23,511
Retained Earnings$408,384$412,444
Minority Interest$6,665$5,999
Total Stockholder's Equity$181,064$176,810
Total Liabilities and Equity$349,493$336,758
\n", "
\n", " " ], "text/plain": [ "FinancialStatements(income_statements=IncomeStatements(statements={Timestamp('2014-12-31 00:00:00'): IncomeStatementData(revenue=364763.0, cogs=234856.0, sga=12002.0, int_exp=286.0, tax_exp=18015.0, rd_exp=0.0, dep_exp=0, other_op_exp=64857.0, gain_on_sale_invest=-5.0, gain_on_sale_asset=3151.0, impairment=0.0, op_exp=94156.0, ebit=34082.0, ebt=51630.0, net_income=32520.0), Timestamp('2015-12-31 00:00:00'): IncomeStatementData(revenue=241406.0, cogs=163605.0, sga=10961.0, int_exp=311.0, tax_exp=5415.0, rd_exp=0.0, dep_exp=0, other_op_exp=32834.0, gain_on_sale_invest=-42.0, gain_on_sale_asset=226.0, impairment=0.0, op_exp=61843.0, ebit=14435.0, ebt=21966.0, net_income=16150.0)}), balance_sheets=BalanceSheets(statements={Timestamp('2014-12-31 00:00:00'): BalanceSheetData(cash=4616.0, st_invest=0.0, receivables=18541.0, inventory=16678.0, lt_invest=20543.0, def_tax_st=2001.0, other_current_assets=1564.0, gross_ppe=0, dep=194121.0, goodwill=0.0, def_tax_lt=3955.0, other_lt_assets=19417.0, payables=25286.0, current_lt_debt=770.0, st_debt=16698.0, lt_debt=11278.0, deferred_rev=0.0, tax_liab_st=39230.0, other_current_liab=13651.0, tax_liab_lt=39230.0, deposit_liab=0.0, other_lt_liab=27111.0, common_stock=10792.0, minority_interest=6665.0, other_income=-18957.0, retained_earnings=408384.0, cash_and_st_invest=4616.0, total_current_assets=52910.0, net_ppe=252668.0, total_non_current_assets=296583.0, total_assets=349493.0, total_current_liab=64633.0, total_debt=29121.0, total_non_current_liab=77619.0, total_liab=168429.0, total_equity=181064.0, total_liab_and_equity=349493.0), Timestamp('2015-12-31 00:00:00'): BalanceSheetData(cash=3705.0, st_invest=0.0, receivables=13243.0, inventory=16245.0, lt_invest=20611.0, def_tax_st=1329.0, other_current_assets=1469.0, gross_ppe=0, dep=195732.0, goodwill=0.0, def_tax_lt=3421.0, other_lt_assets=18498.0, payables=18074.0, current_lt_debt=558.0, st_debt=18204.0, lt_debt=18687.0, deferred_rev=0.0, tax_liab_st=36818.0, other_current_liab=11401.0, tax_liab_lt=36818.0, deposit_liab=0.0, other_lt_liab=26582.0, common_stock=11612.0, minority_interest=5999.0, other_income=-23511.0, retained_earnings=412444.0, cash_and_st_invest=3705.0, total_current_assets=42623.0, net_ppe=251605.0, total_non_current_assets=294135.0, total_assets=336758.0, total_current_liab=53976.0, total_debt=38687.0, total_non_current_liab=82087.0, total_liab=159948.0, total_equity=176810.0, total_liab_and_equity=336758.0)}), forecasts={})" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts[['2014-12-31', '2015-12-31']]" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 181064.0\n", "2015-12-31 176810.0\n", "dtype: float64" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts[['2014-12-31', '2015-12-31']].total_equity" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Calculating FCFs Using `finstmt`\n", "\n", "I have already built FCF calculation into the package. So we can simply do:" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 15916.0\n", "2016-12-31 18605.0\n", "2017-12-31 10385.0\n", "2018-12-31 28094.0\n", "2019-09-30 21350.0\n", "dtype: float64" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.fcf" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we wanted to calculate it manually for some reason, it would also be much easier. This not only because all the items are consolidated, but also calculating lags and changes is easier." ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 252668\n", "2015-12-31 251605\n", "2016-12-31 244224\n", "2017-12-31 252630\n", "2018-12-31 247101\n", "2019-09-30 257065\n", "dtype: int64" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.net_ppe" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 252668.0\n", "2016-12-31 251605.0\n", "2017-12-31 244224.0\n", "2018-12-31 252630.0\n", "2019-09-30 247101.0\n", "dtype: float64" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.lag('net_ppe', 1)" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 -1063.0\n", "2016-12-31 -7381.0\n", "2017-12-31 8406.0\n", "2018-12-31 -5529.0\n", "2019-09-30 9964.0\n", "dtype: float64" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.change('net_ppe')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Also the components of FCF are precalculated." ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2014-12-31 NaN\n", "2015-12-31 15916.0\n", "2016-12-31 18605.0\n", "2017-12-31 10385.0\n", "2018-12-31 28094.0\n", "2019-09-30 21350.0\n", "dtype: float64" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmts.net_income + stmts.non_cash_expenses - stmts.change('nwc') - stmts.capex" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Other Notes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`finstmt` is a general package which can work with a variety of data providers' financial statements. So far, I have confirmed it working with statements from Stockrow and from Capital IQ. Now you can write one set of code that will work regardless of where you are getting your data.\n", "\n", "There may still be some rough edges. I put this package together in a couple days. It also has features for forecasting that we will cover in our next lecture. Please let me know if you run into any issues with the package." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.5" } }, "nbformat": 4, "nbformat_minor": 4 }